package com.bringway.p2p.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.p2p.dao.CzDao;
import com.bringway.p2p.dao.DqbdDao;
import com.bringway.p2p.dao.YcxiDao;

/**
 * 
 * @author qjy
 * @version 创建时间：2016年10月10日 上午10:25:42
 * @功能：查询异常信息
 */

@Repository
public class DqbdDaoImpl  extends P2PJdbcDao implements DqbdDao{
	Logger logger = Logger.getLogger(DqbdDaoImpl.class);
	
	public List<Map<String, Object>> queryDqbdList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String yhm=params.optString("yhm");
		String sjhm=params.optString("sjhm");
		String cgt=params.optString("cgt");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append(" select t.ID,t.USER_NAME,t.CHANGE_TYPE,t.CHANGE_DQ_JE,t.OCCUR_JE,t.DQ_ORIGINAL_JE,t.OCCUR_CHANGE_ID,t.DQ_FINAL_JE,date_format(t.CHANGE_DATE,'%Y-%m-%d %H:%i:%s') CHANGE_DATE,t1.SJHM from t_zhfw_dq_details_log t");
		sql.append(" left join t_zhfw_user_tgcode t1 on t1.USER_NAME=t.USER_NAME");
		sql.append(" where 1=1  ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(t.CHANGE_DATE,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(t.CHANGE_DATE,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if (StringUtils.isNotEmpty(cgt)) {
			sql.append(" AND t.CHANGE_TYPE=:cgt");
			paramMap.put("cgt", cgt);
		}
		if(StringUtils.isNotEmpty(yhm)){
			sql.append(" AND t.USER_NAME like :yhm");
			paramMap.put("yhm", "%"+yhm+"%");
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND t1.SJHM like :sjhm");
			paramMap.put("sjhm", "%"+sjhm+"%");
		}
		sql.append(" ORDER BY t.CHANGE_DATE DESC");
		if(0 != limit){
			sql.append(" LIMIT :START,:LIMIT");
			paramMap.put("START", start);
			paramMap.put("LIMIT", limit);
		}
		logger.info("queryDqbdList sql is " + sql.toString());
		logger.info("queryDqbdList paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);

		
	}

	@Override
	public int gettDqbdCount(JSONObject params) {
		String yhm = params.optString("yhm");
		String sjhm = params.optString("sjhm");
		String cgt=params.optString("cgt");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append(" SELECT count(*)  ");
		sql.append(" FROM t_zhfw_dq_details_log t");
		sql.append(" left join t_zhfw_user_tgcode t1 on t1.USER_NAME=t.USER_NAME");
		sql.append(" where 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(t.CHANGE_DATE,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(t.CHANGE_DATE,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if (StringUtils.isNotEmpty(cgt)) {
			sql.append(" AND t.CHANGE_TYPE=:cgt");
			paramMap.put("cgt", cgt);
		}
		if(StringUtils.isNotEmpty(yhm)){
			sql.append(" AND t.USER_NAME like :yhm");
			paramMap.put("yhm", "%"+yhm+"%");
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND t1.SJHM like :sjhm");
			paramMap.put("sjhm", "%"+sjhm+"%");
		}
		logger.info("gettDqbdCount sql is " + sql.toString());
		logger.info("gettDqbdCount paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}



}
